using System;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Collections.Generic;
using System.Xml;
using T.FAS.Runtime.Base.Entity;

namespace T.FAS.Runtime.DataAccess.SQLDom
{
    /// <summary>
    /// Select SQL语句类
    /// </summary>
    /// <remarks>Select SQL语句</remarks>
    public class SelectSqlStatement : SqlStatement
    {
        #region 常量

        /// <summary>
        /// SelectSqlStatement
        /// </summary>
        public const string SELECTSQLSTATEMENT = "SelectSqlStatement";

        /// <summary>
        /// JoinCondition 
        /// </summary>
        public const string JOINCONDITION = "JoinCondition";

        /// <summary> 
        /// FilterCondition
        /// </summary>
        public const string FILTERCONDITION = "FilterCondition";

        /// <summary> 
        /// OrderByCondition 
        /// </summary>
        public const string ORDERBYCONDITION = "OrderByCondition";

        /// <summary>
        /// MainFromItem 
        /// </summary>
        public const string MAINFROMITEM = "MainFromItem";

        #endregion

        #region 字段

        /// <summary>
        /// Select语句中的核心查询主体
        /// </summary>
        private FromItem mainFromItem;

        /// <summary>
        /// 返回获取数据的前多少条，默认值-1
        /// </summary>
        private int topSize = -1;

        protected DBCustomTools DBCustomTools;

        #endregion

        #region 构造函数

        /// <summary>
        /// 构造函数
        /// </summary>
        public SelectSqlStatement()
            : base()
        {
            From = new From();
            mainFromItem = new FromItem();
            //From.ChildCollection.Add(mainFromItem);
            SelectList = new SelectFieldListStatement();
            JoinCondition = new JoinConditionStatement();
            FilterCondition = new FilterConditionStatement();
            OrderByCondition = new ConditionStatement();
            StaticOrderByCondition = new OrderByStatement();
            AggregateCondition = new AggregateStatement();
            HavingCondition = new HavingStatement();
            GroupByCondition = new GroupByStatement();
            DictFieldAliasMapping = new Dictionary<string, string>();
            DBCustomTools = new DBCustomTools(this);
        }

        #endregion

        #region 属性

        /// <summary>
        /// 查询字段列表
        /// </summary>
        public SelectFieldListStatement SelectList { get; set; }

        /// <summary>
        /// 去重
        /// </summary>
        public Distinct Distinct { get; set; } = new Distinct();

        /// <summary>
        /// From子句
        /// </summary>
        public From From { get; set; }

        /// <summary>
        /// 查询中主表的From语句项。
        /// </summary>
        public FromItem MainFromItem
        {
            get
            {
                return this.mainFromItem;
            }
            private set
            {
                this.mainFromItem = value;
            }
        }

        /// <summary>
        /// 多表的连接条件，目前没啥用，tbaleA join tableB ON tableA.ColA = tbaleB.ColB 的条件放在了JoinConditionItem中
        /// </summary>
        public JoinConditionStatement JoinCondition { get; set; }

        /// <summary>
        /// 过滤条件
        /// </summary>
        public FilterConditionStatement FilterCondition { get; set; }

        /// <summary>
        /// 排序条件:动态传入
        /// </summary>
        public ConditionStatement OrderByCondition { get; set; }

        /// <summary>
        /// 排序条件：静态
        /// </summary>
        public OrderByStatement StaticOrderByCondition { get; set; }

        #region 分组&聚合改动
        /// <summary>
        /// 聚合函数，启用后SelectList会失效
        /// </summary>
        public AggregateStatement AggregateCondition { get; set; }

        /// <summary>
        /// 分组函数，启用后SelectList会失效
        /// </summary>
        public GroupByStatement GroupByCondition { get; set; }

        public HavingStatement HavingCondition { get; set; }
        #endregion

        /// <summary>
        /// 返回获取数据的前多少条，默认值-1
        /// </summary>
        public int TopSize
        {
            get
            {
                return topSize;
            }
            set
            {
                topSize = value;
            }
        }

        public int PageCount { get; set; }

        public int PageIndex { get; set; }

        /// <summary>
        /// 如果使用字段别名，那么此属性表示映射后的顺序号
        /// </summary>
        public int AliasCount { get; set; }

        /// <summary>
        /// 字段别名映射
        /// </summary>
        public Dictionary<string, string> DictFieldAliasMapping { get; set; }

        #endregion

        #region 方法

        /// <summary>
        /// 转换成SQL语句
        /// todo: 将生成的sql字符串加入缓存提高效率
        /// </summary>
        /// <returns>Select SQL语句</returns>
        public override string ToSQL()
        {
            StringBuilder sqlStrBuilder = new StringBuilder();

            string selectItemsSql = GetSelectItemsSQL();
            string fromTillConditionSql = GetFromAndConditionSQL();

            //注入Distinct关键词，如果有
            sqlStrBuilder.Append($"SELECT {Distinct.ToSQL()} {selectItemsSql} {fromTillConditionSql}");

            #region order by
            if (OrderByCondition != null && (OrderByCondition.ChildCollection.Count > 0 || !string.IsNullOrEmpty(OrderByCondition.ConditionString)))
            {
                var orderbyCondition = OrderByCondition.ToSQL();
                if (!string.IsNullOrWhiteSpace(orderbyCondition))
                {
                    sqlStrBuilder.AppendFormat(" {0} \r\n", orderbyCondition);
                }
            }
            else if (StaticOrderByCondition != null && StaticOrderByCondition.ChildCollection.Count > 0)
            {
                foreach (var item in StaticOrderByCondition.ChildCollection)
                {
                    if (item is OrderByField orderbyField)
                    {
                        orderbyField.FieldName = this.SqlBuildingInfo.WrapByCurrentDBEscapeCharacter(orderbyField.FieldName);   //加入``包裹列名
                        if (orderbyField.Table != null)
                        {
                            orderbyField.Table.FieldNameWraps = this.SqlBuildingInfo.WrapByCurrentDBEscapeCharacter;
                        }
                    }
                }
                var orderbyCondition = StaticOrderByCondition.ToSQL();
                if (!string.IsNullOrWhiteSpace(orderbyCondition))
                {
                    sqlStrBuilder.AppendFormat(" {0} ", orderbyCondition);
                }
            }
            else
            {
                if (PageCount > 0 && (OrderByCondition == null || OrderByCondition.ChildCollection.Count <= 0)
                                  && (StaticOrderByCondition == null || StaticOrderByCondition.ChildCollection.Count <= 0))
                    throw new Exception(CommonStringClassSQLDom.SelectSqlStatement_ToSQL);
            }
            #endregion

            return sqlStrBuilder.ToString();
        }

        /// <summary>
        /// 生成Select Items项SQL
        /// </summary>
        /// <param name="isPaging">分页的情况下，MainSQL的Select Item已经包裹过转义符，不需要再次包裹</param>
        /// <returns></returns>
        protected string GetSelectItemsSQL(bool isPaging = false)
        {
            //注册当前SqlStatement运行时所属的DB类型
            this.SqlBuildingInfo.DatabaseType = DBCustomTools.CurrentDB;
            #region FAS低代码改动：修复DO字段列名为 INT,DECIMAL这类MySQL | MSSQL关键词时，查询使用AS别名时报错的bug
            if (!isPaging)
            {
                foreach (var item in SelectList.ChildCollection)
                {
                    if (item is SelectListField selectItem)
                    {
                        if (selectItem.Table != null)
                        {
                            selectItem.Table.FieldNameWraps = this.SqlBuildingInfo.WrapByCurrentDBEscapeCharacter;
                        }
                        if (!string.IsNullOrEmpty(selectItem.FieldAlias))
                        {
                            selectItem.FieldAlias = this.SqlBuildingInfo.WrapByCurrentDBEscapeCharacter(selectItem.FieldAlias);   //加入``包裹列名
                        }
                        if (!string.IsNullOrEmpty(selectItem.FieldName))
                        {
                            //当非表达式列时做系统转义处理
                            if (!selectItem.IsExpress)
                                selectItem.FieldName = this.SqlBuildingInfo.WrapByCurrentDBEscapeCharacter(selectItem.FieldName);   //加入``包裹列名
                        }
                    }
                }
                if (AggregateCondition != null && AggregateCondition.ChildCollection.Count > 0)   //防止聚合列名被编辑为MySQL关键词
                {
                    foreach (var aggItem in AggregateCondition.ChildCollection)
                    {
                        if (aggItem is AggregateField aggField)
                        {
                            if (!string.IsNullOrEmpty(aggField.Alias))
                            {
                                aggField.Alias = this.SqlBuildingInfo.WrapByCurrentDBEscapeCharacter(aggField.Alias);  //加入``包裹列名
                            }
                            if (!string.IsNullOrEmpty(aggField.FieldName))
                            {
                                aggField.FieldName = this.SqlBuildingInfo.WrapByCurrentDBEscapeCharacter(aggField.FieldName);  //加入``包裹列名
                            }
                            if (aggField.Table != null)
                            {
                                aggField.Table.FieldNameWraps = this.SqlBuildingInfo.WrapByCurrentDBEscapeCharacter;
                            }
                        }
                    }
                }
                if (GroupByCondition != null && GroupByCondition.ChildCollection.Count > 0)   //防止分组列名被编辑为MySQL关键词
                {
                    foreach (var group in GroupByCondition.ChildCollection)
                    {
                        if (group is GroupByField gField)
                        {
                            if (!string.IsNullOrEmpty(gField.Alias))
                            {
                                gField.Alias = this.SqlBuildingInfo.WrapByCurrentDBEscapeCharacter(gField.Alias);  //加入``包裹列名
                            }
                            if (!string.IsNullOrEmpty(gField.FieldName))
                            {
                                gField.FieldName = this.SqlBuildingInfo.WrapByCurrentDBEscapeCharacter(gField.FieldName);  //加入``包裹列名
                            }
                            if (gField.Table != null)
                            {
                                gField.Table.FieldNameWraps = this.SqlBuildingInfo.WrapByCurrentDBEscapeCharacter;
                            }
                        }
                    }
                }
            }
            #endregion

            string sql_selectItem;  //select项
            if (AggregateCondition != null && AggregateCondition.ChildCollection.Count > 0)
            {
                if (SelectList != null && SelectList.ChildCollection.Count > 0)
                    sql_selectItem = $" {AggregateCondition.ToSQL()},{SelectList.ToSQL()} \r\n";
                else
                    sql_selectItem = $" {AggregateCondition.ToSQL()} \r\n";
            }
            else
                sql_selectItem = $" {SelectList.ToSQL()} \r\n";
            return sql_selectItem;
        }

        /// <summary>
        /// 生成From到Where条件部分的SQL
        /// </summary>
        /// <returns></returns>
        protected string GetFromAndConditionSQL()
        {
            StringBuilder sqlStrBuilder = new StringBuilder();

            //注册sql转义符，将表名进行转义，以避免纯数字表名的bug
            From.FieldNameWraps = this.SqlBuildingInfo.WrapByCurrentDBEscapeCharacter;
            //todo:这样加sql只会在最后一个from table中加入with(nolock)
            string fromSQL = From.ToSQL() + DBCustomTools.SpecialAppendSQL();
            sqlStrBuilder.Append($" FROM {fromSQL}");

            foreach (SqlElement element in MainFromItem.ChildCollection)
            {
                if (element is InnerJoinItem inner)
                {
                    inner.FieldNameWraps = this.SqlBuildingInfo.WrapByCurrentDBEscapeCharacter;
                }
                if (element is LeftJoinItem left)
                {
                    left.FieldNameWraps = this.SqlBuildingInfo.WrapByCurrentDBEscapeCharacter;
                }
                string joinItemSQL = element.ToSQL();
                //为每一个join table加入 with(nolock) 语句
                joinItemSQL = DBCustomTools.Format(joinItemSQL);
                sqlStrBuilder.AppendLine(joinItemSQL);
            }

            var joinCondition = JoinCondition.ToSQL();  //没啥用
            if (!string.IsNullOrWhiteSpace(joinCondition))
            {
                sqlStrBuilder.AppendFormat("WHERE {0} \r\n", joinCondition);
            }

            //where
            FilterCondition.FieldNameWraps = this.SqlBuildingInfo.WrapByCurrentDBEscapeCharacter;
            var filterCondition = FilterCondition.ToSQL();
            if (!string.IsNullOrWhiteSpace(filterCondition))
            {
                if (string.IsNullOrWhiteSpace(joinCondition))
                {
                    sqlStrBuilder.AppendFormat(" WHERE {0} \r\n", filterCondition);
                }
                else
                {
                    sqlStrBuilder.AppendFormat(" AND {0} ", filterCondition);
                }
            }

            //group
            if (GroupByCondition != null && GroupByCondition.ChildCollection.Count > 0)
            {
                var condition = GroupByCondition.ToSQL();
                if (!string.IsNullOrWhiteSpace(condition))
                {
                    sqlStrBuilder.AppendFormat(" Group by {0} \r\n", condition);
                }
            }

            //having
            if (HavingCondition != null && HavingCondition.ChildCollection.Count > 0)
            {
                var condition = HavingCondition.ToSQL();
                if (!string.IsNullOrWhiteSpace(condition))
                {
                    sqlStrBuilder.AppendFormat(" Having {0} \r\n", condition);
                }
            }
            return sqlStrBuilder.ToString();
        }

        /// <summary>
        /// 生成SQL语句的总条数SQL
        /// </summary>
        /// <returns>Select SQL语句</returns>
        protected string GetPagingCountSQL()
        {
            if (this.PageIndex <= 0 || this.PageCount <= 0) return default;
            string pageCountSQL;
            if (Distinct.IsEnabled
                || (AggregateCondition != null && AggregateCondition.ChildCollection.Count > 0)
                || (GroupByCondition != null && GroupByCondition.ChildCollection.Count > 0)
                || (HavingCondition != null && HavingCondition.ChildCollection.Count > 0)
                )
            {
                string sql_selectItem = GetSelectItemsSQL(true);
                string fromTillConditionSql = GetFromAndConditionSQL();
                //注入Distinct关键词，如果有
                pageCountSQL = $" select count(1) from (select {Distinct.ToSQL()} {sql_selectItem} {fromTillConditionSql}) _$a ";
            }
            else
            {
                pageCountSQL = $" select count(*) as _totalCount { GetFromAndConditionSQL() }";
            }

            return pageCountSQL;
        }

        /// <summary>
        /// 克隆
        /// </summary>
        /// <returns>查询SQL副本</returns>
        public override object Clone()
        {
            var newObject = base.Clone() as SelectSqlStatement;
            if (SelectList != null)
                newObject.SelectList = SelectList.Clone() as SelectFieldListStatement;
            if (From != null)
                newObject.From = From.Clone() as From;
            if (JoinCondition != null)
                newObject.JoinCondition = JoinCondition.Clone() as JoinConditionStatement;
            if (OrderByCondition != null)
                newObject.OrderByCondition = OrderByCondition.Clone() as ConditionStatement;
            if (StaticOrderByCondition != null)
                newObject.StaticOrderByCondition = StaticOrderByCondition.Clone() as OrderByStatement;
            if (FilterCondition != null)
                newObject.FilterCondition = FilterCondition.Clone() as FilterConditionStatement;
            if (MainFromItem != null)
                newObject.MainFromItem = MainFromItem.Clone() as FromItem;

            newObject.AliasCount = AliasCount;
            newObject.DictFieldAliasMapping = new Dictionary<string, string>();
            foreach (KeyValuePair<string, string> map in DictFieldAliasMapping)
            {
                newObject.DictFieldAliasMapping.Add(map.Key, map.Value);
            }

            return newObject;
        }

        #endregion
    }
}
